var db = require('../common/db');
var mysql = require('mysql');

exports.getTags = function (callback) {
    db.execSql("select * from tag", null, callback);
};

exports.create = function (task, tags, callback) {
    db.execSql("INSERT INTO Task SET ?", task, function (err, data) {
        for (var i = 0; i < tags.length; i++) {
            var tag = {TaskId: data.insertId, TagId: tags[i]};
            db.execSql("INSERT INTO TaskTag SET ?", tag);
        }
        if (callback != null) {
            callback();
        }
    });
};

exports.query = function (condition, callback) {
    var rowStart = (condition.PageNo - 1) * condition.PageSize;

    var where = " where task.ProjectId = " + condition.ProjectId;

    if (condition.Keyword != null && condition.Keyword.length > 0) {
        where += " and (task.TaskName like " + mysql.escape("%" + condition.Keyword + "%") + " or task.TaskId like " + mysql.escape(condition.Keyword) + ") ";
    }
    if (condition.StatusList != null && condition.StatusList.length > 0) {
        where += " and task.Status in (" + condition.StatusList.join(',') + ") ";
    }
    if (condition.PriorityList != null && condition.PriorityList.length > 0) {
        where += " and task.Priority in (" + condition.PriorityList.join(',') + ") ";
    }
    if (condition.AssignTo != null) {
        where += " and task.AssignTo = " + mysql.escape(condition.AssignTo);
    }
    if (condition.TagList != null && condition.TagList.length > 0) {
        where += " and tag.TagId in (" + condition.TagList.join(',') + ") ";
    }

    var sql = " select SQL_NO_CACHE SQL_CALC_FOUND_ROWS task.*,user.Nick as AssignToUser, ";
    sql += " Group_concat(tag.TagName) as TagNames, ";
    sql += " Group_concat(tag.TagId) as TagIds ";
    sql += " from task ";
    sql += " left join tasktag on task.TaskId = tasktag.TaskId ";
    sql += " left join tag on tasktag.TagId=tag.TagId ";
    sql += " left join user on task.AssignTo=user.UserId ";
    sql += where;
    sql += " group by task.TaskId ";
    sql += " order by DATE_FORMAT(task.DeadLine,'%Y%m%d'),task.Priority desc ";
    sql += " limit ?,? ;";
    sql += " select FOUND_ROWS() as rowCount ; ";

    var params = [rowStart, condition.PageSize];
    db.execSql(sql, params, function (err, data) {
        var rowCount = data[1][0].rowCount;
        callback(data[0], rowCount);
    });
};

exports.update = function (task, callback) {
    db.execSql("update task set TaskName=?,DeadLine=?,Priority=?,Status=?,Description=?,AssignTo=? where TaskId=?",
        [
            task.TaskName,
            task.DeadLine,
            task.Priority,
            task.Status,
            task.Description,
            task.AssignTo,
            task.TaskId
        ]
        , function (err, data) {
            if (data.changedRows > 0) {
                callback(true);
            } else {
                callback(false);
            }
        });
};

exports.get = function (taskId, callback) {
    var sql = " select task.*, ";
    sql += " createUser.Nick as CreatorNick, ";
    sql += " assignToUser.Nick as AssignToUser, ";
    sql += " Group_concat(tag.TagName) as TagNames, ";
    sql += " Group_concat(tag.TagId) as TagIds ";
    sql += " from task ";
    sql += " inner join user as createUser on task.Creator = createUser.UserId ";
    sql += " inner join user as assignToUser on task.AssignTo = assignToUser.UserId ";
    sql += " left join tasktag on task.TaskId = tasktag.TaskId ";
    sql += " left join tag on tasktag.TagId=tag.TagId ";
    sql += " where task.TaskId = ? ";
    db.execSql(sql, taskId, function (err, data) {
        if (data.length > 0) {
            callback(data[0]);
        }
        else {
            callback(null);
        }
    });
};